import openpyxl
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
from utils.dateUtil import workDays
import datetime

def gen_excel():
    wb = openpyxl.Workbook()
    ws = wb.active

    # 设置font
    font = Font(name="宋体", size=12, color="FFFFFF")

    # 背景色
    fill = PatternFill("solid", fgColor="9999FF")
    # 设置单元格border的style
    side = Side(border_style="thin", color="FFFFFF")
    border = Border(left=side, right=side, top=side, bottom=side)

    align = Alignment(horizontal='center', vertical='center', wrap_text=True)

    title = ['报告日期', '审批', '项目名称', '迭代', '机能', '机种', '模块', '计划工时', '实际工时',
             '是否为加班', '计划完成日', '整体完成状况', '当日完成状况',
             {'one': 'WBS', 'two': ['WBS1', 'WBS2', 'WBS3', 'WBS4', 'WBS5', 'WBS6'], 'end': '说明'}]

    list_length = 0
    for i, item in enumerate(title):
        if isinstance(item, str):
            cell_width = len(item) * 4
            font_point = chr(i + 65)

            ws.column_dimensions['{}'.format(font_point)].width = cell_width
            ws.row_dimensions[1].height = 30

            # 合并单元格
            ws.merge_cells(font_point + '1' + ':' + font_point + '2')

            cell = ws[font_point + '1']
            cell2 = ws[font_point + '2']
            cell.font = font
            cell.value = item
            cell.fill = fill
            cell.border = border
            cell2.border = border
            cell.alignment = align
        else:
            # dict
            len_merge = len(item['two']) - 1
            font_point = chr(i + 65)
            merge_point = chr(i + len_merge + 65)

            ws.merge_cells(font_point + '1' + ':' + merge_point + '1')
            ws.row_dimensions[1].height = 15
            # 合并单元格
            cell = ws[font_point + '1']
            cell.font = font
            cell.value = item['one']
            cell.fill = fill

            cell.alignment = align
            for j, val in enumerate(item['two']):
                second_point = chr(i + j + 65)
                cell2 = ws[second_point + '2']
                ws.column_dimensions['{}'.format(second_point)].width = 10
                cell2.font = font
                cell2.value = val
                cell2.fill = fill
                cell2.border = border
                cell2.alignment = align

            len_end = len(item['two'])
            end_point = chr(i + len_end + 65)
            # 合并单元格
            ws.merge_cells(end_point + '1' + ':' + end_point + '2')
            ws.column_dimensions['{}'.format(end_point)].width = 50

            cell3 = ws[end_point + '1']
            cell4 = ws[end_point + '2']
            cell3.font = font
            cell3.value = item['end']
            cell3.fill = fill
            cell3.border = border
            cell4.border = border
            cell3.alignment = align


            list_length = i + len_end + 1

    gen_body_list(ws, list_length)

    wb.save('export.xlsx')

def gen_body_list(ws, list_length):
    # 表数据
    start_end = workDays.get_month_b_e_day('201911')
    startdate = datetime.datetime(int(start_end[0][0]), int(start_end[0][1]), int(start_end[0][2]))
    enddate = datetime.datetime(int(start_end[1][0]), int(start_end[1][1]), int(start_end[1][2]))
    work = workDays(startdate, enddate)  # 需要传入两个datetime格式日期

    for row_index, item in enumerate(work.workDays()):  # 获取每一个工作日期
        gen_row_data(item, row_index, list_length, ws)

def gen_row_data(date, row_index, list_length, ws):
    # 背景色
    fill = PatternFill("solid", fgColor="C0C0C0")
    # 设置font
    font = Font(name="宋体", size=12)
    side = Side(border_style="thin", color="333399")
    border = Border(left=side, right=side, top=side, bottom=side)
    align = Alignment(horizontal='left', vertical='top', wrap_text=True)

    row_point = row_index + 3

    null_font = ['D', 'E', 'F', 'G', 'K', 'P', 'Q', 'R', 'S']
    input_vals = [{
        'C': '合同管理',
        'H': 8,
        'I': 8,
        'J': '否',
        'L': '100%',
        'M': '当日计划完成',
        'N': '开发',
        'O': '合同管理',
        'T': '呵呵\nas;ld了了弗拉了方离开发'
    }]

    for cell_index in range(list_length):
        cell_point = chr(cell_index + 65)
        point = cell_point + str(row_point)
        cell = ws[point]
        cell.border = border
        cell.font = font
        cell.alignment = align

        if cell_point in null_font:
            cell.value = None
        elif cell_point == 'A':
            cell.value = date.strftime('%Y%m%d')
        elif cell_point == 'B':
            cell.fill = fill
            cell.value = '未审批'
        else:
            if cell_point == 'T':
                cell.alignment = Alignment(wrapText=True)
            # input_vals[row_index]
            input = input_vals[0][cell_point]
            cell.value = input

if __name__ == '__main__':
    try:
        gen_excel()
        print('生成xlsx')
    except Exception:
        print('报错')
